#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time         : 17-11-28 下午6:42
# @Author       : xiaohan

import re
from sqlalchemy import create_engine, Integer, Column, String, DateTime, Float, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import pandas as pd

'''
    http://danielweitzenfeld.github.io/passtheroc/blog/2014/10/12/datasci-sqlalchemy/
'''

engine = create_engine('mysql+pymysql://root:123456@101.200.59.101:3306/opinion?charset=utf8', echo=True, pool_size=10)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()


def data_frame(query, columns, index_col):
    def make_row(x):
        return dict([(c, getattr(x, c)) for c in columns])

    df = pd.DataFrame([make_row(x) for x in query])

    df.index = df[index_col]
    return df


'''
    interval:'d' - 天，'m' - 月， 'q' - 季，'y' - 年
'''


def get_fundamentals(query, obj, entry_date=None, interval='1d', report_quarter=False, index_col='id', ):
    df = data_frame(query, [c.name for c in obj.__table__.columns], index_col='EndDate')
    print(df)

    if report_quarter:
        df = df.loc[:, ['InfoSource','EPS', 'BasicEPS']]
    else:
        df = df.loc[:, ['BasicEPS', 'EPS']]
    # 这里要操作的字段选择出来,避免一些其他无用的字段出现错误影响
    # 这里操作时间序列的操作
    if interval is not None:
        ti, lm = re.findall('(\d+)(\w)', interval)[0]
        if lm in ['y', 'Y']:
            lm = 'a'
        ts = df.resample(lm.upper()).sum().tail(int(ti)).dropna(axis=0, how='all')
        print(ts)


if __name__ == '__main__':
    new_ = {'EndDate': Column(DateTime(50)), 'CompanyCode': Column(Integer), 'EPS': Column(Float(50)),
            'InfoSource': Column(String(50)), 'BasicEPS': Column(Float(50)), 'Mark': Column(Integer),
            'ID': Column(Integer, primary_key=True, autoincrement=True),
            '__tablename__': 'LC_MainDataNew'}
    Info = type('Info', (Base,), new_)
    # 'id', 'metadata', 'name'
    get_fundamentals(session.query(Info).filter(Info.CompanyCode == 3).filter(Info.Mark == 2).all(), Info,
                     interval='10q', report_quarter=True)
